/*
 * User: Sepheus Angelus
 * Date: 28/02/2008
 * Time: 15:39
 * 
 */

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Data.Odbc;

namespace Aphyros_MUD_Server
{
	/// <summary>
	/// Provides access to the database.
	/// </summary>
	public class Database
	{
		#region Private Fields
		private static OdbcConnection _Conn;
		private static OdbcCommand _Command;
		private static OdbcDataReader _DataReader;
		private static string _Driver = "DRIVER=";
		private static string _Server = "SERVER=";
		private static string _Port = "PORT=";
		private static string _Database = "DATABASE=";
		private static string _UID = "UID=";
		private static string _Password = "PASSWORD=";
		private static string _Option = "OPTION=";
		#endregion

		#region Properties
		/// <summary>
		/// Sets the database driver value to the given driver.
		/// </summary>
		public static string Driver
		{
			set { _Driver += value += ";"; }
		}
		/// <summary>
		/// Sets the location to connect to.
		/// </summary>
		public static string Server
		{
			set { _Server += value += ";"; }
		}
		/// <summary>
		/// Sets the port number to connect to.
		/// </summary>
		public static string Port
		{
			set { _Port += value += ";"; }
		}
		/// <summary>
		/// Sets the schema to use.
		/// </summary>
		public static string Schema
		{
			set { _Database += value += ";"; }
		}
		/// <summary>
		/// Sets the username to log in with on the database.
		/// </summary>
		public static string UID
		{
			set { _UID += value += ";"; }
		}
		/// <summary>
		/// Sets the password to log in with on the database.
		/// </summary>
		public static string Password
		{
			set { _Password += value += ";"; }
		}
		/// <summary>
		/// Sets the option that influences what data is returned.
		/// </summary>
		/// <remarks>
		/// Possible options
		/// * 1 - Client Can't handle the real column width being returned.
		/// * 2 - Have MySQL return found rows value.
		/// * 8 - Allow Big Values.
		/// * 32 - Toggle Dynamic cursor support.
		/// * 16384 - Convert LongLong to Int.
		/// Options are set by the total sum of the options desired.
		/// Recommended value is 3.
		/// </remarks>
		public static string Option
		{
			set { _Option += value += ";"; }
		}
		#endregion
		
		#region Public Methods
		/// <summary>
		/// Initiates the connection to the Aphyros database.
		/// </summary>
		public static bool Connect()
		{
			try
			{
				string ConnectionString = _Driver + _Server + _Port +_Database + _UID + _Password + _Option;
				_Conn = new OdbcConnection(ConnectionString);
				_Conn.Open();
				Console.ForegroundColor = ConsoleColor.Cyan;
				Console.WriteLine("Successful connection to database {0} at {1} ",_Conn.Database,_Conn.DataSource);
				return true;
			}
			catch(OdbcException e)
			{
				Console.ForegroundColor = ConsoleColor.Red;
				Console.WriteLine("Failed. {0}",e.Message);
				return false;
			}
		}
		
		
		#region Adding Data functions.
		/// <summary>
		/// Adds a user to the database.
		/// </summary>
		/// <param name="Name">The user name to be added.</param>
		/// <param name="Password">The password associated with the user name.</param>
		/// <returns>True on success of adding user.</returns>
		public static bool AddUser(string Name, string Password)
		{
			string Query = "INSERT INTO users (Name,Class,Password) VALUES ('"+Name+"',"+"0,'"+Password+"')";
			try
			{
				CheckConnection();
				_Command = new OdbcCommand(Query,_Conn);
				Console.ForegroundColor = ConsoleColor.Cyan;
				Console.WriteLine("Adding user {0}",Name);
				_Command.ExecuteNonQuery();
				return true;
			}
			catch(OdbcException e)
			{
				Console.ForegroundColor = ConsoleColor.Red;
				Console.WriteLine("Possible error in query {0}",Query);
				Console.WriteLine("Unable to create user {0}.",Name);
				Console.WriteLine(e.Message);
				return false;
			}
		}
		
		/// <summary>
		/// Adds a skill for the given user.
		/// </summary>
		/// <param name="UserID">The ID of the user to give the skill to.</param>
		/// <param name="SkillID">The skill ID of the skill to give</param>
		/// <returns>True on success, False on any problems.</returns>
		public static bool AddSkill(uint UserID, byte SkillID)
		{
			string Query = "INSERT INTO userskills (UserID,SkillID) VALUES ("+UserID+","+SkillID+")";
			try
			{
				CheckConnection();
				_Command = new OdbcCommand(Query,_Conn);
				_Command.ExecuteNonQuery();
				return true;
			}
			catch(OdbcException e)
			{
				Console.ForegroundColor = ConsoleColor.Red;
				if(!e.ToString().Contains("23000"))
				{
					Console.WriteLine("Possible error in query {0}",Query);
					Console.WriteLine("Unable to add skill for ID {0}.",UserID);
					Console.WriteLine(e.Message);
				}
				return false;
			}
		}
		
		/// <summary>
		/// Adds a list of skills for the given user.
		/// </summary>
		/// <param name="UserID">The ID of the user to give the skills to.</param>
		/// <param name="SkillID">List of skills to bestow</param>
		/// <returns>True on success, false on failure.</returns>
		public static bool AddSkill(uint UserID, List<byte> SkillID)
		{
			StringBuilder Query = new StringBuilder("INSERT INTO userskills (UserID,SkillID) VALUES ("+UserID+","+SkillID[0]+")");
			for(int i = 1; i<SkillID.Count; i++)
			{
				Query.Append(",("+UserID+","+SkillID[i]+")");
			}
			try
			{
				CheckConnection();
				_Command = new OdbcCommand(Query.ToString(),_Conn);
				_Command.ExecuteNonQuery();
				return true;
			}
			catch(OdbcException e)
			{
				Console.ForegroundColor = ConsoleColor.Red;
				if(!e.ToString().Contains("23000"))
				{
					Console.WriteLine("Possible error in query {0}",Query.ToString());
					Console.WriteLine("Unable to add skill for ID {0}.",UserID);
					Console.WriteLine(e.Message);
				}
				return false;
			}
		}
		
		#endregion
		
		#region Getting information functions.
		/// <summary>
		/// Returns whether a user exists or not.
		/// </summary>
		/// <param name="Name">Name of the user to check for.</param>
		/// <returns>True or False, True if found; False if not.</returns>
		public static bool GetUser(string Name)
		{
			try
			{
				CheckConnection();
				string Query = "SELECT COUNT(Name) FROM users WHERE Name = \""+Name+"\"";
				_Command = new OdbcCommand(Query,_Conn);
				if(Convert.ToInt32(_Command.ExecuteScalar()) > 0) { return true; }
				return false;
			}
			catch(OdbcException e)
			{
				Console.ForegroundColor = ConsoleColor.Red;
				Console.WriteLine("Unable to obtain user information for user {0}",Name);
				Console.WriteLine(e.Message);
				return false;
			}
			catch(Exception e)
			{
				Console.WriteLine(e.Message);
				return false;
			}
		}
		
		/// <summary>
		/// Returns whether a user exists or not.
		/// </summary>
		/// <param name="Name">Name of the user to check for.</param>
		/// <param name="Password">Password of the user.</param>
		/// <returns>True if the username and password match the database.</returns>
		public static bool GetUser(string Name, string Password)
		{
			try
			{
				CheckConnection();
				string Query = "SELECT COUNT(Name) FROM users WHERE Name = \""+Name+"\" and Password = \""+Password+"\";";
				_Command = new OdbcCommand(Query,_Conn);
				if(Convert.ToInt32(_Command.ExecuteScalar()) > 0)
				{
					Console.ForegroundColor = ConsoleColor.Magenta;
					Console.WriteLine("{0} logged in.",Name);
					return true;
				}
				return false;
			}
			catch(OdbcException e)
			{
				Console.ForegroundColor = ConsoleColor.Red;
				Console.WriteLine("Unable to obtain user information for user {0}",Name);
				Console.WriteLine(e.Message);
				return false;
			}
		}
		
		/// <summary>
		/// Gets the name of the class of a given player.
		/// </summary>
		/// <param name="Name">Name of the player to query.</param>
		/// <returns>Name of the class on success, NONE on failure.</returns>
		public static string GetClassName(string Name)
		{
			try
			{
				string Result="";
				CheckConnection();
				string Query = "SELECT Class FROM users WHERE Name = \""+Name+"\";";
				_Command = new OdbcCommand(Query,_Conn);
				_DataReader = _Command.ExecuteReader();
				while(_DataReader.Read())
				{
					Result = Classes.Name[_DataReader.GetByte(0)];
				}
				_DataReader.Close();
				return Result;
			}
			catch(OdbcException)
			{
				return "NONE";
			}
		}
		#endregion
		
		#region Loading Functions
		/// <summary>
		/// Reads information about the user from the database.
		/// </summary>
		/// <param name="user">The user to read the information about.</param>
		public static void LoadUser(Listener user)
		{
			try
			{
				CheckConnection();
				string Query = "SELECT * FROM users WHERE Name = \""+user.Name+"\"";
				_Command = new OdbcCommand(Query,_Conn);
				_DataReader = _Command.ExecuteReader();
				while(_DataReader.Read())
				{
					user.UniqueID = (uint)_DataReader.GetInt32(0);
					user.Class = _DataReader.GetByte(2);
					user.Access = _DataReader.GetByte(6);
					user.Gender = _DataReader.GetByte(7);
				}
				_DataReader.Close();
				Console.ForegroundColor = ConsoleColor.Magenta;
				Console.WriteLine("Loaded user {0}",user.Name);
			}
			catch(OdbcException e)
			{
				Console.ForegroundColor = ConsoleColor.Red;
				Console.WriteLine("Unable to load user information for user {0}",user.Name);
				Console.WriteLine(e.Message);
			}
			
		}
		
		/// <summary>
		/// Loads all the skills into memory from the database.
		/// </summary>
		/// <returns>True on success, False if problems are encountered.</returns>
		public static bool LoadSkills()
		{
			try
			{
				CheckConnection();
				string Query = "SELECT * FROM skills";
				_Command = new OdbcCommand(Query,_Conn);
				_DataReader = _Command.ExecuteReader();
				while(_DataReader.Read())
				{
					Skills.SkillID.Add(_DataReader.GetByte(0));
					Skills.Name.Add(_DataReader.GetString(1));
					Skills.Effect.Add(_DataReader.GetString(2));
					Skills.Description.Add(_DataReader.GetString(3));
				}
				_DataReader.Close();
				Console.ForegroundColor = ConsoleColor.Cyan;
				Console.WriteLine("Successfully loaded skills.");
				return true;
			}
			catch(OdbcException e)
			{
				Console.ForegroundColor = ConsoleColor.Red;
				Console.WriteLine("Unable to load skills into memory.");
				Console.WriteLine(e.Message);
				return false;
			}
		}
		
		/// <summary>
		/// Loads all the Rooms into memory from the database.
		/// </summary>
		/// <returns>True on success, False if problems are encountered.</returns>
		public static bool LoadRooms()
		{
			try
			{
				CheckConnection();
				string Query = "SELECT * FROM rooms";
				_Command = new OdbcCommand(Query,_Conn);
				_DataReader = _Command.ExecuteReader();
				while(_DataReader.Read())
				{
					Rooms.RoomID.Add(_DataReader.GetInt32(0));
					Rooms.Description.Add(_DataReader.GetString(1));
					Rooms.North.Add(_DataReader.GetInt32(2));
					Rooms.East.Add(_DataReader.GetInt32(3));
					Rooms.South.Add(_DataReader.GetInt32(4));
					Rooms.West.Add(_DataReader.GetInt32(5));
				}
				_DataReader.Close();
				Console.ForegroundColor = ConsoleColor.Cyan;
				Console.WriteLine("Successfully loaded rooms.");
				return true;
			}
			catch(OdbcException e)
			{
				Console.ForegroundColor = ConsoleColor.Red;
				Console.WriteLine("Unable to load rooms into memory.");
				Console.WriteLine(e.Message);
				return false;
			}
		}
		
		/// <summary>
		/// Loads all of the classes into memory.
		/// </summary>
		/// <returns>True on success, False if there are any problems.</returns>
		public static bool LoadClasses()
		{
			try
			{
				CheckConnection();
				string Query = "SELECT * FROM classes";
				_Command = new OdbcCommand(Query,_Conn);
				_DataReader = _Command.ExecuteReader();
				while(_DataReader.Read())
				{
					Classes.ClassID.Add(_DataReader.GetByte(0));
					Classes.Male_Title.Add(_DataReader.GetString(1));
					Classes.Female_Title.Add(_DataReader.GetString(2));
					Classes.Name.Add(_DataReader.GetString(3));
					Classes.Description.Add(_DataReader.GetString(4));
					Classes.Skill1.Add(_DataReader.GetByte(5));
					Classes.Skill2.Add(_DataReader.GetByte(6));
					Classes.Skill3.Add(_DataReader.GetByte(7));
					Classes.Skill4.Add(_DataReader.GetByte(8));
					Classes.Skill5.Add(_DataReader.GetByte(9));
					Classes.Defence.Add((uint)_DataReader.GetInt32(10));
					Classes.Health.Add((uint)_DataReader.GetInt32(11));
					Classes.Stamina.Add((uint)_DataReader.GetInt32(12));
					Classes.EXP.Add(_DataReader.GetFloat(13));
					Classes.Attack.Add((uint)_DataReader.GetInt32(14));
					Classes.Mana.Add((uint)_DataReader.GetInt32(15));
				}
				_DataReader.Close();
				Console.ForegroundColor = ConsoleColor.Cyan;
				Console.WriteLine("Successfully loaded classes.");
				return true;
			}
			catch(OdbcException e)
			{
				Console.ForegroundColor = ConsoleColor.Red;
				Console.WriteLine("Unable to load classes into memory.");
				Console.WriteLine(e.Message);
				return false;
			}
		}
		#endregion
		
		/// <summary>
		/// Closes the database connection.
		/// </summary>
		public static void Close()
		{
			_Conn.Close();
		}
		#endregion
		
		#region Private Methods
		private static void CheckConnection()
		{
			if(_Conn.State.ToString() == "Closed")
			{
				Console.ForegroundColor = ConsoleColor.Yellow;
				Console.WriteLine("Connection lost.  Reconnecting...");
				_Conn.Open();
				if(_Conn.State.ToString() == "Open")
				{
					Console.ForegroundColor = ConsoleColor.Cyan;
					Console.WriteLine("Successfully reconnected.");
				}
				else
				{
					Console.ForegroundColor = ConsoleColor.Red;
					Console.WriteLine("Failed to reconnect.");
				}
			}
		}
		#endregion
	}
}
